import json
import pymysql
from datetime import datetime

# ====== 数据库配置 ======
DB_CONFIG = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "jintiankeji666@123",
    "database": "cloud-android",
    "charset": "utf8mb4"
}

# ====== JSON 文件路径 ======
JSON_FILE = "output2.json"

# ====== 连接数据库 ======
conn = pymysql.connect(**DB_CONFIG)
cursor = conn.cursor()

# ====== 读取 JSON 文件 ======
with open(JSON_FILE, "r", encoding="utf-8") as f:
    content = json.load(f)

if "data" not in content or not content["data"]:
    print("❌ JSON 数据中未找到 data 字段或为空")
    exit(1)

for item in content["data"]:
    sql = """
    INSERT INTO real_phone_template2 (
        brand, model, fingerprint_md5, fingerprint, resource_specification_code,
        screen_layout_code, adi_template_download_url, adi_template_pwd, property_json,
        android_image_version, delete_flag, create_by, create_time, update_by, update_time,
        device_name, is_official, status, adi_template_version, model_code, is_public,
        aosp_version, test_cases_download_url
    ) VALUES (
        %(brand)s, %(model)s, %(fingerprint_md5)s, %(fingerprint)s, %(resource_specification_code)s,
        %(screen_layout_code)s, %(adi_template_download_url)s, %(adi_template_pwd)s, %(property_json)s,
        %(android_image_version)s, %(delete_flag)s, %(create_by)s, %(create_time)s, %(update_by)s, %(update_time)s,
        %(device_name)s, %(is_official)s, %(status)s, %(adi_template_version)s, %(model_code)s, %(is_public)s,
        %(aosp_version)s, %(test_cases_download_url)s
    )
    """

    # 转换时间
    def parse_time(t):
        if not t:
            return None
        try:
            # 处理 ISO 格式，如 "2025-10-09T02:26:38.000+00:00"
            return datetime.fromisoformat(t.replace("Z", "+00:00"))
        except Exception:
            return None

    params = {
        "brand": item.get("brand", ""),
        "model": item.get("model", ""),
        "fingerprint_md5": item.get("fingerprintMd5", ""),
        "fingerprint": item.get("fingerprint", ""),
        "resource_specification_code": item.get("resourceSpecificationCode", ""),
        "screen_layout_code": item.get("screenLayoutCode", ""),
        "adi_template_download_url": item.get("adiTemplateDownloadUrl", ""),
        "adi_template_pwd": item.get("adiTemplatePwd", ""),
        "property_json": json.dumps(item.get("propertyJSON", {}), ensure_ascii=False),
        "android_image_version": item.get("androidImageVersion", 0),
        "delete_flag": int(item.get("deleteFlag", False)),
        "create_by": item.get("createBy", ""),
        "create_time": parse_time(item.get("createTime")),
        "update_by": item.get("updateBy", ""),
        "update_time": parse_time(item.get("updateTime")),
        "device_name": item.get("deviceName", ""),
        "is_official": item.get("isOfficial", 0),
        "status": item.get("status", 0),
        "adi_template_version": item.get("adiTemplateVersion", ""),
        "model_code": item.get("modelCode", ""),
        "is_public": item.get("isPublic", 1),
        "aosp_version": item.get("aospVersion", ""),
        "test_cases_download_url": item.get("testCasesDownloadUrl", "")
    }

    cursor.execute(sql, params)
    print(f"✅ 已插入: {item.get('brand')} {item.get('model')}")

conn.commit()
cursor.close()
conn.close()
print("🎉 所有数据插入完成")
